//
//  CPSQLTools.m
//  ChinaPoem
//
//  Created by 赵俊明 on 2019/3/4.
//  Copyright © 2019 赵俊明. All rights reserved.
//

#import "CPSQLTools.h"
#import "FMDB.h"
#import "CPModel.h"
#import "NSString+CPString.h"

static CPSQLTools *_SQLTool = nil;
@interface CPSQLTools()<NSCopying,NSMutableCopying>
{FMDatabase *_CPdb;}
@end

@implementation CPSQLTools

+ (instancetype)cp_sharedInstance
{
    if (_SQLTool == nil) {
        _SQLTool = [[CPSQLTools alloc] init];
        [_SQLTool initDataBase];
    }
    return _SQLTool;
}
+ (instancetype)allocWithZone:(struct _NSZone *)zone
{
    if (_SQLTool == nil) {_SQLTool = [super allocWithZone:zone];}
    return _SQLTool;
}
- (id)copy {return self;}
- (id)mutableCopy {return self;}
- (id)copyWithZone:(NSZone *)zone {return self;}
- (id)mutableCopyWithZone:(NSZone *)zone {return self;}
- (void)initDataBase
{
    NSString *filePath = [JMDocumentsPath stringByAppendingPathComponent:@"ChinaPoem.sqlite"];
    _CPdb = [FMDatabase databaseWithPath:filePath];
    if ([_CPdb open]){ // 创表
        // 1、历史
        NSString *history =@"create table if not exists history(id integer primary key autoincrement not null,pk integer, dynasty text, title text, type text, author text, explanation text, content text, appreciation text, timeNum text);";
        [_CPdb executeUpdate:history];
        // 2、收藏
        NSString *save =@"create table if not exists save(id integer primary key autoincrement not null,pk integer, dynasty text, title text, type text, author text, explanation text, content text, appreciation text, timeNum text);";
        [_CPdb executeUpdate:save];
        [_CPdb close];
    }
}

#pragma mark - **************** neww
- (NSMutableArray *)cp_searchPoem:(NSString *)sql likeName:(NSString *)name column:(NSString *)column
{
    [_CPdb open];
    FMResultSet *reset = [_CPdb executeQuery:sql,name];
    NSMutableArray *dataSource = [NSMutableArray array];
    while ([reset next]) {
        [dataSource addObject:denyNil([reset stringForColumn:column])];
    }
    
    [_CPdb close];
    return dataSource;
}

- (NSMutableArray *)cp_search:(NSString *)tName column:(NSString *)column
{
    [_CPdb open];
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select * from %@ order by random() limit 8;",tName]];
    NSMutableArray *dataSource = [NSMutableArray array];
    while ([reset next]) {
        [dataSource addObject:denyNil([reset stringForColumn:column])];
    }
    
    [_CPdb close];
    return dataSource;
}

- (NSMutableArray *)cp_shareRandom:(NSInteger)count share:(BOOL)isShare
{
    NSString *sql = [NSString stringWithFormat:@"select * from share order by random() limit %ld;",(long)count];
    [_CPdb open];
    FMResultSet *reset = [_CPdb executeQuery:sql];
    NSMutableArray *items = [NSMutableArray array];
    while ([reset next]) {
        CPModel *model = [CPModel new];
        model.cp_ids = [reset stringForColumn:@"pk"];
        model.cp_title = [reset stringForColumn:@"title"];
        model.cp_type = [reset stringForColumn:@"type"];
        model.cp_author = [reset stringForColumn:@"author"];
        NSString *content = [reset stringForColumn:@"content"];
        model.cp_content = isShare ? [content cp_author:model.cp_author dynasty:model.cp_dynasty title:model.cp_title]:content;
        [items addObject:model];
    }
    [_CPdb close];
    return items;
}

- (NSMutableArray *)cp_shareData:(NSString *)sql
{
    [_CPdb open];
    FMResultSet *reset = [_CPdb executeQuery:sql];
    NSMutableArray *dataSource = [NSMutableArray array];
    while ([reset next]) {
        CPModel *model = [CPModel new];
        model.cp_ids = [reset stringForColumn:@"pk"];
        model.cp_title = [reset stringForColumn:@"title"];
        model.cp_type = [reset stringForColumn:@"type"];
        model.cp_author = [reset stringForColumn:@"author"];
        NSString *content = [reset stringForColumn:@"content"];
        model.cp_content = [content cp_author:model.cp_author dynasty:model.cp_dynasty title:model.cp_title];
        [dataSource addObject:model];
    }
    
    [_CPdb close];
    return dataSource;
}

// 查询数据表
- (CPModel *)cp_queryData:(NSString *)tableName ids:(NSString *)ids
{
    [_CPdb open];
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select * from %@ where id = '%@'", tableName, ids]];
    CPModel *model = [[CPModel alloc] init];
    while ([reset next]) {
        model.cp_ids = [reset stringForColumn:@"pk"];
        model.cp_dynasty = [reset stringForColumn:@"dynasty"];
        model.cp_title = [reset stringForColumn:@"title"];
        model.cp_type = [reset stringForColumn:@"type"];
        model.cp_author = [reset stringForColumn:@"author"];
        model.cp_timeTsp = [reset stringForColumn:@"timeNum"];
    }
    [_CPdb close];
    return model;
}

// 查询数据库
- (NSMutableArray *)cp_query_poem:(NSString *)sql
{
    [_CPdb open];
    NSMutableArray *data = [NSMutableArray array];
    FMResultSet *reset = [_CPdb executeQuery:sql];
    while ([reset next]) {
        CPModel *model = [CPModel new];
        model.cp_ids = [NSString stringWithFormat:@"%d", [reset intForColumn:@"pk"]];
        model.cp_dynasty = [reset stringForColumn:@"dynasty"];
        model.cp_title = [reset stringForColumn:@"title"];
        model.cp_type = [reset stringForColumn:@"type"];
        model.cp_author = [reset stringForColumn:@"author"];
        model.cp_content = [[reset stringForColumn:@"content"] cp_author:model.cp_author dynasty:model.cp_dynasty title:model.cp_title];
        model.cp_explanation = [[reset stringForColumn:@"explanation"] cp_replaceString];
        model.cp_appreciation = [[reset stringForColumn:@"appreciation"] cp_replaceString];
        [data insertObject:model atIndex:0];
    }
    
    [_CPdb close];
    return data;
}

- (NSMutableArray *)cp_authorInfo:(NSString *)author
{
    [_CPdb open];
    NSString *sql = [NSString stringWithFormat:@"select * from author where name = '%@';", author];
    FMResultSet *reset = [_CPdb executeQuery:sql];
    NSMutableArray *items = [NSMutableArray array];
    while ([reset next]) {
        CPModel *model = [[CPModel alloc] init];
        NSString *introduce = [reset stringForColumn:@"introduction"];
        model.cp_content = [introduce stringByReplacingOccurrencesOfString:@"&quot;" withString:@""];
        model.cp_author = [reset stringForColumn:@"name"];
        model.cp_title = [reset stringForColumn:@"xingpy"];
        [items addObject:model];
    }
    
    [_CPdb close];
    return items;
}

- (NSMutableArray *)cp_authorByAlpha:(NSString *)alpha
{
    [_CPdb open];
    NSMutableArray *items = [NSMutableArray array];
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select * from author where xingpy ='%@'",alpha]];
    while ([reset next]) {
        CPModel *model = [[CPModel alloc] init];
        NSString *introduce = [reset stringForColumn:@"introduction"];
        model.cp_content = [introduce stringByReplacingOccurrencesOfString:@"&quot;" withString:@""];
        model.cp_author = [reset stringForColumn:@"name"];
        model.cp_title = [reset stringForColumn:@"xingpy"];
        [items addObject:model];
    }
    
    [_CPdb close];
    return items;
}

- (NSMutableArray *)cp_all_authorInfo
{
    [_CPdb open];
    NSArray *alphas = @[@"a",@"b",@"c",@"d",@"e",@"f",@"g",@"h",@"i",@"j",@"k",@"l",@"m",@"n",@"o",@"p",@"q",@"r",@"s",@"t",@"u",@"v",@"w",@"x",@"y",@"z"];
    NSMutableArray *models = [NSMutableArray array];
    for (NSString *obj in alphas) {
        NSMutableArray *items = [NSMutableArray array];
        FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select * from author where xingpy ='%@' order by random() limit(20)",[obj uppercaseString]]];
        while ([reset next]) {
            CPModel *model = [[CPModel alloc] init];
            NSString *introduce = [reset stringForColumn:@"introduction"];
            model.cp_content = [introduce stringByReplacingOccurrencesOfString:@"&quot;" withString:@""];
            model.cp_author = [reset stringForColumn:@"name"];
            model.cp_title = [reset stringForColumn:@"xingpy"];
            [items addObject:model];
        }
        if (items.count > 0) {
            CPHistoryModel *hMmdel = [[CPHistoryModel alloc] init];
            hMmdel.models = items;
            hMmdel.alpha = [obj uppercaseString];
            [models addObject:hMmdel];
        }
    }
    [_CPdb close];
    return models;
}

- (void)cp_delete:(CPModel *)model tName:(NSString *)tableName
{
    [_CPdb open];
    NSString *insert = [NSString stringWithFormat:@"delete from %@ where pk = '%@'",tableName,model.cp_ids];
    [_CPdb executeUpdate:insert];
    [_CPdb close];
}

- (void)cp_insert:(CPModel *)model tName:(NSString *)tableName save_sql:(NSString *)sql
{
    [_CPdb open];
    BOOL find = NO;
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select pk from %@ where pk = '%@'",tableName,model.cp_ids]];
    while ([reset next]) {
        find = YES;
        break;
    }
    if (!find) {
        NSString *sqlInsert = [NSString stringWithFormat:@"insert into %@ (pk, dynasty, title, type, author, explanation, content, appreciation,timeNum) values ('%@','%@','%@','%@','%@','%@','%@','%@','%@');", tableName, model.cp_ids, model.cp_dynasty, model.cp_title, model.cp_type, model.cp_author, model.cp_explanation,model.cp_content,model.cp_appreciation, [NSString cp_tsp]];
        [_CPdb executeUpdate:sqlInsert];
    }
    [_CPdb close];
}

//model.cp_ids, model.cp_dynasty, model.cp_title, model.cp_type, model.cp_author
- (NSMutableArray *)cp_historyORSave:(NSString *)tabName
{
    [_CPdb open];
    NSMutableArray *data = [NSMutableArray array];
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select * from %@;",tabName]];
    while ([reset next]) {
        CPModel *model = [CPModel new];
        model.cp_ids = [NSString stringWithFormat:@"%d", [reset intForColumn:@"pk"]];
        model.cp_dynasty = [reset stringForColumn:@"dynasty"];
        model.cp_title = [reset stringForColumn:@"title"];
        model.cp_type = [reset stringForColumn:@"type"];
        model.cp_author = [reset stringForColumn:@"author"];
        model.cp_content = [[reset stringForColumn:@"content"] cp_author:model.cp_author dynasty:model.cp_dynasty title:model.cp_title];
        model.cp_explanation = [[reset stringForColumn:@"explanation"] cp_replaceString];
        model.cp_appreciation = [[reset stringForColumn:@"appreciation"] cp_replaceString];
        model.cp_timeTsp = [NSString cp_tsp:[reset stringForColumn:@"timeNum"]];
        [data insertObject:model atIndex:0];
    }
    [_CPdb close];
    return data;
}

- (BOOL)checkIsSave:(CPModel *)model
{
    [_CPdb open];
    BOOL find = NO;
    FMResultSet *reset = [_CPdb executeQuery:[NSString stringWithFormat:@"select pk from save where pk = '%@'",model.cp_ids]];
    while ([reset next]) {
        find = YES;
        break;
    }
    [_CPdb close];
    return find;
}
@end
